CREATE OR REPLACE PACKAGE BODY pkg_device_info
IS

	PROCEDURE get_device_ip (
        p_flag          OUT   PLS_INTEGER
	   ,p_ret_msg       OUT   VARCHAR2 
       ,p_device_id        IN VARCHAR2
	   ,p_device_sn      OUT  PLS_INTEGER
	   ,p_ip_addr        OUT  VARCHAR2
	)
	AS 
		v_ip_addr  VARCHAR2(15 BYTE) ; -- device_list.ip_addr%TYPE;
		v_ip_port  PLS_INTEGER; -- device_list.port_no%TYPE; 
		v_device_sn PLS_INTEGER := 0 ;
		
	BEGIN 
		SELECT device_sn  
			  ,ip_addr    
			  ,port_no    		
		 INTO  v_device_sn
		      ,v_ip_addr
			  ,v_ip_port
		FROM  device_list 
		WHERE device_id = p_device_id
		AND   status in ('N','W')
		;

		p_flag := 0;
		p_ret_msg:= 'OK';
		p_device_sn  := v_device_sn;
		p_ip_addr    := v_ip_addr ||':'||v_ip_port ; 
	EXCEPTION  
		WHEN NO_DATA_FOUND THEN 
			p_flag := 1;
			p_ret_msg:= 'No Device Found for Device ID: '||p_device_id;
		
		WHEN OTHERS THEN 
			p_device_sn := -1 ;
			p_ret_msg:= 'Unknown error : '||sqlerrm () ;
	
	END get_device_ip;
	
    PROCEDURE new_device (
       p_flag    OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_device_id     VARCHAR2
      ,p_device_type   VARCHAR2 
	  ,p_SIM           VARCHAR2 
	  ,p_pond_id       PLS_INTEGER 
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_ct           PLS_INTEGER := 0 ;

		v_data_type    dev_outgoing_log.data_type%TYPE  := '2'; --  
		            -- xxxxxxxxxxx,xxxxxxxxxxx   -- 11 位电话号码
		            -- 类型为2的数据定义为： （1），（2）， 如果电话号码全为0， 则该报警电话无效。

	BEGIN
	

		MERGE INTO device_list  dl 
		USING (
			SELECT  p_device_id    device_id        
                   ,p_pond_id      pond_id   
                   ,p_device_type  device_type       
                   ,p_SIM          SIM                
            FROM dual 
          ) s 
        ON ( dl.device_id = s.device_id)
        WHEN MATCHED THEN UPDATE SET 
             dl.pond_id     = s.pond_id
            ,dl.device_type = s.device_type 
            ,dl.sim         = s.sim 
            ,dl.status      = 'N'
		WHERE  nvl(dl.pond_id,0 ) = 0 
        WHEN NOT MATCHED THEN INSERT (
							  device_sn    
							 ,device_id    
							 ,pond_id      
							 ,device_type  
							 ,SIM          
							)
				VALUES ( device_list_sn_seq.NEXTVAL 
                        ,s.device_id
						,s.pond_id 
						,s.device_type
						,s.SIM
						);
	/*
		INSERT INTO device_list (
							  device_sn    
							 ,device_id    
							 ,pond_id      
							 ,device_type  
							 ,SIM          
							 ,status       
							)
				VALUES ( device_list_sn_seq.NEXTVAL 
                        ,p_device_id
						,p_pond_id 
						,p_device_type
						,p_SIM
						,'N');
						
	*/
	
		
		v_ct := SQL%ROWCOUNT; 

		INSERT INTO dev_outgoing_log (
									 dev_outgoing_logid  
									,device_sn           
									,device_id           
									,created_by          
									,data_type           
									,outgoing_data       
			)
		  SELECT   dev_outgoing_log_seq.nextval 
			     , device_sn
				 , device_id
				 , 'App-new_device'
				 , v_data_type
				 , to_char(u.account, 'fm00000000000')||','|| to_char(nvl(u.alert_number,0), 'fm00000000000') 
			FROM device_list d
			INNER JOIN pond_info p USING (pond_id)
			INNER JOIN user_info u USING (user_id)
			WHERE d.device_id =  p_device_id
			  AND pond_id     =  p_pond_id 
			;
		
		COMMIT;
		
		p_flag := 0;
		p_ret_msg  := '设备添加成功,ID:'||p_device_id||',绑定到塘口ID：'||p_pond_id||', '||v_ct ||' row Merged';
		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }' ;
		
		RETURN;
	EXCEPTION
		WHEN DUP_VAL_ON_INDEX THEN 
			p_flag := -1;
			p_ret_msg  := '添加失败，相同DeviceID已经存在';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }' ;
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '添加设备失败，未知错误：'||replace( sqlerrm(), '"', '');
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'" }' ;
			
	END new_device ;

   PROCEDURE device_list (
       p_flag    OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id       PLS_INTEGER 
      )
	IS
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	

	BEGIN
	
		select  -- device_id,  device_type, sim, pond_id 
			  '['||
                listagg(  '{ "device_id":"'|| device_id   ||'"'||
                          ',"device_type":"'||device_type ||'"'||
						  ',"sim":"'||sim ||'"'||
						  ',"pond_id":"'||pond_id  ||'"'||
						  ',"status":"'||status  ||'"'||
						  '}' , ',' ) 
					  WITHIN GROUP ( order by device_sn  DESC )
                  ||']'
			INTO 	v_msg 		
		from device_list 
		inner join pond_info 
		using (pond_id)
		where user_id = p_user_id ;
						
		p_flag := 0;
		
		p_ret_msg := '查询设备清单成功';

		p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'","data": '||v_msg ||'}' ;
		
		
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  1;
			p_ret_msg  := '查询失败，该用户名下没有任何设备';
 		    p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'","data": '||v_msg ||'}' ;
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '查询设备失败，未知错误：'||replace( sqlerrm(), '"', '');
		    p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'","data": '||v_msg ||'}' ;
			
	END device_list ;



   PROCEDURE modify_device (
       p_flag    OUT   PLS_INTEGER
	  ,p_ret_msg       OUT   VARCHAR2 
	  ,p_data_msg      OUT   VARCHAR2     -- can be null
      ,p_user_id       PLS_INTEGER 
	  ,p_pond_id         PLS_INTEGER 
	  ,p_o_pond_id       PLS_INTEGER 
	  ,p_device_id       VARCHAR2
	  ,p_device_type     VARCHAR2
	  ,p_o_device_type   VARCHAR2
	  ,p_SIM             VARCHAR2
	  ,p_o_SIM           VARCHAR2
	  ,p_status          VARCHAR2
	  ,p_o_status        VARCHAR2
      )
	IS
		v_ct           PLS_INTEGER := 0 ;
		v_device_sn    PLS_INTEGER := 0 ;
	    v_flag         PLS_INTEGER := 0 ;
		v_msg          VARCHAR2(4000);	
	BEGIN
	
		SELECT COUNT(1), MAX ( device_sn) 
			INTO v_ct , v_device_sn
		FROM device_list 
		LEFT JOIN pond_info 
			USING (pond_id)
		WHERE (  user_id = p_user_id or user_id is null   )
		  AND device_id = p_device_id;
			
		IF v_ct = 0 THEN 
			p_flag :=  -1;
			p_ret_msg  := '修改失败，当前用户名下找不到设备:'||p_device_id||'，请检查设备ＩＤ';
		
		ELSIF v_ct > 1 THEN 
			p_flag :=  -2;
			p_ret_msg  := '修改失败，当前用户名下找到多个相同设备:'||p_device_id||'，请与系统管理员联系';
		ELSE   ---- now  v_ct is garanteend = 1 
			
			
			UPDATE device_list
				SET  pond_id     = p_pond_id     			  
					,device_type = p_device_type 
					,SIM         = p_SIM         			  
					,status      = p_status      
			WHERE  device_id     = p_device_id 
			  AND  decode ( nvl(pond_id,0), p_o_pond_id     ,0, 1 ) = 0 
              AND  decode ( device_type , p_o_device_type ,0, 1 ) = 0 
		      AND  decode ( SIM         , p_o_SIM         ,0, 1 ) = 0 
              AND  decode ( status      , p_o_status      ,0, 1 ) = 0 
			  ;

			v_ct :=   SQL%ROWCOUNT;
			--pkg_debug.debug_log( $$PLSQL_UNIT, $$PLSQL_LINE, 'SQL%ROWCOUNT:'||v_ct  ||
			--					',pond_id    :'''||p_o_pond_id    ||''''||
			--					',device_type:'''||p_o_device_type||''''||
			--					',SIM        :'''||p_o_SIM        ||''''||
			--					',status     :'''||p_o_status     ||''''
			--					);
			--  
			  
			IF v_ct = 1 THEN 
				COMMIT;  
				p_flag := 0;
				p_ret_msg := '修改设备成功，已修改设备：'||p_device_id ;
			ELSE  ---now 0 row is updated 
				ROLLBACK ;  
				p_flag := 1;
				p_ret_msg := '修改设备 '||p_device_id||' 失败,设备原始信息不匹配，请检查提供的设备修改前数据';
			
			END IF ;
		END IF ;
				p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;		
		
		RETURN;
	EXCEPTION
		WHEN NO_DATA_FOUND  THEN 
			p_flag :=  1;
			p_ret_msg  := '修改失败，该设备不存在，或设备原始信息不匹配，请检查设备ＩＤ及修改前数据';
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;		
		WHEN OTHERS  THEN 
			p_flag := -2 ;
			p_ret_msg := '修改设备失败，原因未知，请联络系统管理人员。错误信息：'||replace( sqlerrm(), '"', '');
			p_data_msg  := '{"flag": '||p_flag||',"message": "'|| p_ret_msg||'"}' ;		
			
	END modify_device ;
BEGIN
	pkg_debug.SetTopic('PKG_DEVICE_INFO');
			  
END pkg_device_info;
/
